package com.situ.company.score.dao.impl;

import java.util.ArrayList;
import java.util.Arrays;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import com.situ.company.score.dao.IScoreDao;
import com.situ.company.score.model.ScoreModel;
import com.situ.company.util.FmtEmpty;
import com.situ.company.util.JDBCUtil;

public class ScoreDaoImpl implements IScoreDao{
	private static String table = "score";
	private static String cols = " code_emp,code_pro,score ";

	@Override
	public Integer insert(ScoreModel model) {
		String sql=String.format(" insert into %s(%s)values(?,?,?)", table,cols);
		return JDBCUtil.update(sql.toString(),model.getCodeEmp(),model.getCodePro(),model.getScore());
	}

	@Override
	public Integer deleteByCode(ScoreModel model) {
		String sql=String.format(" delete from %s where code_emp=? and code_pro=?", table);
		return JDBCUtil.update(sql, model.getCodeEmp(),model.getCodePro());
	}

	@Override
	public Integer delete(ScoreModel model) {
		StringBuffer sql=new StringBuffer(" delete from ").append(table);
		List<Object> values=appendWhere(sql,model);
		return JDBCUtil.update(sql.toString(), values);
	}


	@Override
	public Integer updateAll(ScoreModel model) {
		String sql=String.format(" update %s set score=? where code_emp=? and code_pro=?", table);
		return JDBCUtil.update(sql, model.getScore(),model.getCodeEmp(),model.getCodePro());
	}

	@Override
	public Integer updateActive(ScoreModel model) {
		StringBuffer sql=new StringBuffer(" update ");
		sql.append(table).append(" set id=id ");
		List<Object> values=appendSet(sql,model);
		return JDBCUtil.update(sql.toString(), values);
	}


	@Override
	public List<ScoreModel> selectList(ScoreModel model) {
		StringBuffer sql=getSqlSel();
		List<Object> values=appendWhere(sql, model);
		Map<String,String> fields=getFields();
		return JDBCUtil.queryList(sql.toString(), values, ScoreModel.class,fields);
	}

	@Override
	public ScoreModel selectModel(ScoreModel model) {
		StringBuffer sql=getSqlSel().append(" where code_emp=? and code_pro=? ");
		List<Object> values=Arrays.asList(model.getCodeEmp(),model.getCodePro());
		Map<String,String> fields=getFields();
		return JDBCUtil.queryModel(sql.toString(), values,ScoreModel.class,fields);
	}
	
	private StringBuffer getSqlSel() {
		StringBuffer sql=new StringBuffer(" select id,code_emp,code_pro,(select name from employee where code_emp=code) name_emp,(select name from project where code_pro=code ) name_pro,score");
		sql.append(" from ").append(table);
		return sql;
	}
	
	private Map<String, String> getFields() {
		Map<String,String> fields=new HashMap<>();
		fields.put("id", "id");
		fields.put("codeEmp", "code_emp");
		fields.put("codePro", "code_pro");
		fields.put("nameEmp", "name_emp");
		fields.put("namePro", "name_pro");
		fields.put("score", "score");
		return fields;
	}

	@Override
	public Integer selectCount(ScoreModel model) {
		StringBuffer sql=new StringBuffer(" select count(1) from score ");
		List<Object> vals = appendWhere(sql, model);
		
		return JDBCUtil.queryInt(sql.toString(), vals);
	}

	private List<Object> appendWhere(StringBuffer sql, ScoreModel model) {
		sql.append(" where 1=1");
		List<Object> values=new ArrayList<Object>();
		String codeEmp=model.getCodeEmp();
		if(!FmtEmpty.isEmpty(codeEmp)) {
			sql.append(" and code_emp like ?");
			values.add(codeEmp);
		}
		String codePro=model.getCodePro();
		if(!FmtEmpty.isEmpty(codePro)) {
			sql.append(" and code_pro like ?");
			values.add(codePro);
		}
		String score=model.getScore();
		if(!FmtEmpty.isEmpty(score)) {
			sql.append(" and score like ?");
			values.add(score);
		}
		if(model.isPageOn()) {
			sql.append(" limit ?,?");
			values.add(model.getRowStart());
			values.add(model.getPageLimit());
		}
		return values;
	}
	
	private List<Object> appendSet(StringBuffer sql, ScoreModel model) {
		List<Object> values=new ArrayList<Object>();
		String score=model.getScore();
		if(score!=null) {
			sql.append(" ,score=?");
			values.add(score);
		}
		values.add(model.getCodeEmp()); 
		values.add(model.getCodePro());
		return values;
	}

}
